package com.fs.base.utils;

import java.io.FileInputStream;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.core.io.FileSystemResource;

/**
 * 读取Excel工具类
 * @author DeveloperKK
 *
 */
public class ExcelUtils {

	/**
	 * 读取excel行数据2003版本
	 * @param path
	 * @return
	 * @throws Exception
	 */
	private static  List<List<String>> readExcel2003(String excelPath) throws Exception {
	    InputStream is = new FileInputStream(excelPath);
	    HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
	    List<List<String>> result = new ArrayList<List<String>>();
	    int size = hssfWorkbook.getNumberOfSheets();
	    // 循环每一页，并处理当前循环页
	    for (int numSheet = 0; numSheet < size; numSheet++) {
	        // HSSFSheet 标识某一页
	        HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
	        if (hssfSheet == null) {
	            continue;
	        }
	        // 处理当前页，循环读取每一行
	        for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
	            HSSFRow hssfRow = hssfSheet.getRow(rowNum);
	            if(hssfRow==null){
	            	continue;
	            }else{
	            	int minColIx = hssfRow.getFirstCellNum();
		            int maxColIx = hssfRow.getLastCellNum();
		            List<String> rowList = new ArrayList<String>();
		            //System.out.println("minColIx: "+ minColIx + " maxColIx:"+ maxColIx);
		           // System.out.println("列宽："+ maxColIx );
		            // 遍历改行，获取处理每个cell元素
		            for (int colIx = minColIx; colIx < maxColIx; colIx++) {
		                // HSSFCell 表示单元格
		                HSSFCell cell = hssfRow.getCell(colIx);
		                //如果数据为空就不添加
		                String value = cellValFormat(cell);
		                rowList.add(value);
		            }
		            if(rowList.size()>0){
		            	result.add(rowList);
		            }
	            }
	        }
	    }
	    return result;
	}
	
	/**
	 * 读取excel行数据2007版本
	 * @param path
	 * @return
	 * @throws Exception
	 */
	 private static List<List<String>> readExcel2007(String excelPath) throws Exception {
        InputStream is = new FileInputStream(excelPath);
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
        List<List<String>> result = new ArrayList<List<String>>();
        // 循环每一页，并处理当前循环页
        for (XSSFSheet xssfSheet : xssfWorkbook) {
            if (xssfSheet == null) {
                continue;
            }
            // 处理当前页，循环读取每一行
            for (int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
                XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                int minColIx = xssfRow.getFirstCellNum();
                int maxColIx = xssfRow.getLastCellNum();
                List<String> rows = new ArrayList<String>();
                for (int colIx = minColIx; colIx < maxColIx; colIx++) {
                    XSSFCell cell = xssfRow.getCell(colIx);
                    String value = cellValFormat(cell);
                    rows.add(value);
                }
                if(rows.size()>0){
	                  result.add(rows);
	            }
            }
        }
        return result;
    }
	 
	public static List<List<String>> readData(String excelPath){
		List<List<String>> data = null;
		if(StringUtils.isNotBlank(excelPath)){
			FileSystemResource file = new FileSystemResource(excelPath);
			if(file.exists()){
				try {
					if(excelPath.matches("^.+\\.(?i)(xls)$")){
						data = readExcel2003(excelPath);
					}else if(excelPath.matches("^.+\\.(?i)(xlsx)$")){
						data =  readExcel2007(excelPath);
					}else{
						System.err.println(file.getFilename() + "非Excle格式，无法解析！");
					}
				} catch (Exception e) {
					e.printStackTrace();
				}
			}else{
				System.err.println(excelPath + "文件不存在！");
			}
		}
		return data;
	}
	
	/**
	 * 单元格值处理
	 * @param cell
	 * @return
	 */
	private static String cellValFormat(Cell cell) {
		String cellVal = "";
		if(cell!=null){
			switch (cell.getCellType()) {
				case Cell.CELL_TYPE_BOOLEAN:
					cellVal =  cell.getBooleanCellValue() ? "true" : "false";
					break;
				case Cell.CELL_TYPE_FORMULA:
					try {
						cellVal = String.valueOf(cell.getStringCellValue());
					} catch (IllegalStateException e) {
						cellVal = String.valueOf(cell.getNumericCellValue());
					}
					break;
				case Cell.CELL_TYPE_NUMERIC:
				try {
					if (HSSFDateUtil.isCellDateFormatted(cell)) {
						SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  
						cellVal = sdf.format(cell.getDateCellValue());
						if(cellVal.endsWith(" 00:00:00")){
							cellVal = cellVal.replace(" 00:00:00", "");
						}
					}else{
						cell.setCellType(Cell.CELL_TYPE_STRING);
						cellVal = cell.getStringCellValue();
					}
				} catch (Exception e) {
					cellVal = String.valueOf(cell.getRichStringCellValue());
				}
					break;
				case Cell.CELL_TYPE_STRING:
					cellVal = cell.getStringCellValue();
					break;
				default: return "";
			}
		}
		if(StringUtils.isNotBlank(cellVal)){
			cellVal = cellVal.trim();
		}
		return cellVal;
    }
	
	/**
	 * 数据集合长度格式化，防止越界异常
	 * @param data
	 * @param length
	 * @return
	 */
	public static List<String> dataLenFomart(List<String>data,int length){
		if(data!=null){
			int len = data.size();
			if(len<length){
				for(int i=len;i<length;i++){
					data.add("");
				}
			}
			if(len>length){
				List<String> temp = new ArrayList<String>();
				for(int i=0;i<length;i++){
					temp.add(data.get(i));
				}
				data = temp;
			}
		}else{
			data = new ArrayList<String>();
			for(int i=0;i<length;i++){
				data.add("");
			}
		}
		return data;
	}
	
	//public static void main(String[] args) throws Exception {
		//List<List<String>> result = readData("D:\\2017项目\\旗天科技\\江浙\\模板数据\\襄阳分公司2016年武汉江浙智能集团专线哑设备改造_立项管理数据录入模板2.xls");
		/*for (List<String> obj: result) {
			System.out.println("长度："+ obj.size() + JSON.toJSONString(obj));
		}*/
		
		//List<Project> pList = Project.excelDataFormat(result);
		//for(Project p: pList){
		//	System.out.println(JSON.toJSONString(p));
		//}
		/*List<String> data = null;
		//data.add("1");data.add("2");data.add("");data.add("5");
		data = dataLenFomart(data, 2);
		System.out.println(JSON.toJSON(data));*/
	//}
}

